<%@page import="java.util.ArrayList"%>
<%@page import="java.util.Vector"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.util.StringTokenizer"%>
<%
    String driver = "org.postgresql.Driver";
    Class.forName(driver).newInstance();

    Connection con = null;
    ResultSet rst = null;
    Statement stmt = null;
    String[] start = null;
    String[] end = null;
    float pathlength = 0;
    int counter = 0;
    String xml=new String();

    //Float[] startPoint = null;
    //Float[] endPoint = null;



    try {
        String url = "jdbc:postgresql://localhost:5433/secondtestgis";
        con = DriverManager.getConnection(url, "postgres", "nitesh");
        stmt = con.createStatement();
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }

    if ((request.getParameter("startpoint")) != null) {
        // Retrieve start point
        start = request.getParameter("startpoint").toString().split(" ");
        //startPoint[0] = new Float(start[0]);
        //startPoint[1] = new Float(start[1]);
        System.out.println("startpoint=" + start[0] + " " + start[1]);
    }
    if ((request.getParameter("finalpoint")) != null) {
        // Retrieve end point
        end = request.getParameter("finalpoint").toString().split(" ");
        //endPoint[0] = new Float(end[0]);
        //endPoint[1] = new Float(end[1]);
        System.out.println("end=" + end[0] + " " + end[1]);
    }

    //String sql = "insert into jpt values(" + start[0] + "," + start[1] + ")";
    //stmt.executeUpdate(sql);
    System.out.println("startfindedge=");
    ArrayList startEdge = findNearestEdge(start);
    System.out.println("source=" + startEdge.remove(1));

    System.out.println("endfindedge=");
    ArrayList endEdge = findNearestEdge(end);
    System.out.println("target=" + endEdge.remove(2));

    System.out.println("finding sp");
    String spsql ="select rt.gid, AsText(rt.the_geom) AS wkt,length(rt.the_geom) AS length,ktm_roads01.gid FROM ktm_roads01,(SELECT gid, the_geom FROM dijkstra_sp_delta('ktm_roads01',1748,1299,3000)) as rt WHERE ktm_roads01.gid=rt.gid;";

    rst=stmt.executeQuery(spsql);
    System.out.println("sp query executed");
// Return route as XML

    xml = "<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>" + "\n";
    xml = xml + "<route>\n";

// Add edges to XML file
    while (rst.next()) {
        //System.out.println("rst is not empty");
        pathlength = pathlength + rst.getFloat("length");
        System.out.println("length="+rst.getFloat("length"));
        xml = xml + "\t<edge id='" + (++counter) + "'>\n";
        xml = xml + "\t\t<gid>" + rst.getInt("gid") + "</gid>\n";
        System.out.println("gid="+rst.getInt("gid"));
        xml = xml + "\t\t<wkt>" + rst.getString("wkt") + "</wkt>\n";
        System.out.println("wkt="+rst.getString("wkt"));
        xml = xml + "\t\t<length>" + (pathlength / 1000) + "</length>\n";
        xml = xml + "\t</edge>\n";
    }
    
    xml = xml + "</route>\n";
    
    response.setContentType("text/xml");
    response.getWriter().write(xml);
    
    //System.out.println(xml);


%>
<%!
// FUNCTION findNearestEdge
    public ArrayList findNearestEdge(String[] lonlat) throws Exception {
        System.out.println("lonlat=" + lonlat[1]);
        String driver = "org.postgresql.Driver";
        Class.forName(driver).newInstance();
        System.out.println("driver loaded");
        Connection con = null;
        ResultSet rst = null;
        Statement stmt = null;
        ArrayList edge = new ArrayList();
        //String edge[]=null;

        try {
            String url = "jdbc:postgresql://localhost:5433/secondtestgis";
            con = DriverManager.getConnection(url, "postgres", "nitesh");
            stmt = con.createStatement();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        System.out.println("db connected");
        // Connect to database
        String sql = "SELECT gid, source, target,the_geom,distance(the_geom, ST_GeometryFromText('POINT("+lonlat[0]+" "+lonlat[1]+")',3857)) AS dist FROM ktm_roads01 WHERE ST_DWithin(the_geom, 'SRID=3857;POINT("+lonlat[0]+" "+lonlat[1]+")', 1000.0)ORDER BY dist LIMIT 1";

        rst = stmt.executeQuery(sql);
        System.out.println("query executed");


        while (rst.next()) {
            //System.out.println("gid=" + rst.getInt(1));
            //System.out.println("source=" + rst.getInt(2));
            //System.out.println("target=" + rst.getInt(3));
            //System.out.println("the_geom=" + rst.getString(4));
            //System.out.println("dist=" + rst.getFloat(5));

            int a = rst.getInt(1);
            edge.add(a);
            //edge[0] = Integer.toString(a);
            //System.out.println("gid=" + edge.remove(0));

            int s = rst.getInt(2);
            edge.add(s);
            //edge[1] = Integer.toString(s);

            int t = rst.getInt(3);
            edge.add(t);
            //edge[2] = Integer.toString(t);

            edge.add(rst.getString(4));

            float d = rst.getFloat(5);
            edge.add(d);
            // edge[4] = Float.toString(d);
        }
        return edge;
    }
%>

